﻿using System;
using System.IO;
using System.Net;
using System.Text;
using System.Drawing;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace MostScrapLibary4Magento
{
    public class WebStoreItem
    {
        public int entity_id { get; set; }
        public string sku { get; set; }
        public string input_description { get; set; }
        public bool status { get; set; }
        public DateTime last_update { get; set; }
        public string supplyer_code { get; set; }
        public decimal price { get; set; }
        public bool is_change { get; set; }
    }

    public class itemPrice
    {
        public string name { get; set; }
        public decimal price1 { get; set; }
        public decimal price2 { get; set; }
        public bool isNew { get; set; }
    }


    public class Most4Magento
    {

        /// <summary>
        /// Деклариране на свойства
        /// променлви и списъци
        /// </summary>
        public int PagesCount = 0;
        public decimal usd = (decimal)1.588;

        public List<itemPrice> MostPriceList = new List<itemPrice>();
        private string baseURL = string.Empty;
        List<WebStoreItem> WebList = new List<WebStoreItem>();


        /// <summary>
        /// Конструктор на управляващия клас на библиотеката. Проверява за успешна връзка със сайта на Мост и брои страниците на ценовата листа.
        /// </summary>
        /// <param name="BaseURL">URL към страницата с ценовата листа на Мост</param>
        public Most4Magento(string BaseURL)
        {
            PagesCount = GetPriceListPagesCount(GetPriceLisetPageSourse(BaseURL));
            baseURL = BaseURL;

            if (PagesCount == 0)
            {
                Debug.Print("PageCouunt e 0 - GetPriseListFromPage se anulira");
                return;
            }
        }


        /// <summary>
        /// Метод за извличане сорс кода на страница
        /// </summary>
        /// <param name="url">Адрес на целевата страница към която се прави заявка за четене</param>
        /// <returns>Връща сорс кода на страницата</returns>
        public string GetPriceLisetPageSourse(string url)
        {
            string strResult = string.Empty;

            try
            {
                WebRequest myRequest = WebRequest.Create(url);
                WebResponse myResponse = myRequest.GetResponse();

                using (StreamReader sr = new StreamReader(myResponse.GetResponseStream()))
                {
                    strResult = sr.ReadToEnd();
                    sr.Close();
                }
            }
            catch (UriFormatException)
            {
                Debug.Print("Problem s formata na URI");
            }
            catch (WebException)
            {
                Debug.Print("Problem sys zarejdane na stranicata");
            }

            return strResult;
        }


        /// <summary>
        /// Метод ползващ се от конструктора на основния клас за броене на страниците в ценоваталиста
        /// </summary>
        /// <param name="pageSourse">Сорс кода на страницата с ценовата листа</param>
        /// <returns>Връща броя страници</returns>
        public int GetPriceListPagesCount(string pageSourse)
        {
            string rex = "(?<=pageNum=)\\d{0,3}";
            int currentPage = 0;
            int maxPage = 0;

            MatchCollection mc = Regex.Matches(pageSourse, rex, RegexOptions.Singleline);

            foreach (Match m in mc)
            {
                if (!int.TryParse(m.ToString(), out currentPage))
                    Debug.Print("Problem s GetPriceListPagesCount - greshka pri konvertirane");
                else
                {
                    if (currentPage > maxPage)
                        maxPage = currentPage;
                }
            }

            if (maxPage > 0) return maxPage;
            else
            {
                Debug.Print("Problem s GetPriceListPagesCount - vryshta rezultat 0");
                return 0;
            }
        }


        /// <summary>
        /// Метод за извличане списък с цените на продуктите от една страница. Използва се от метода GetMostFullPriceList
        /// </summary>
        /// <param name="pageSource">Сорс код на страница</param>
        public void GetMostPagePriceList(string pageSource)
        {
            pageSource = Regex.Replace(pageSource, "\0", "", RegexOptions.Singleline | RegexOptions.IgnoreCase);

            string rexPriceListItem = "(?<=p2='\\d'/></td><td>)(.*?)(?=</td><td align=\"right\">)(.*?)(?=</td><td align=\"right\">)(.*?)(?=</td><td>&nbsp;&nbsp;USD&nbsp;&nbsp;</td>)";
            MatchCollection mcPriceListItem = Regex.Matches(pageSource, rexPriceListItem, RegexOptions.Singleline);

            foreach (Match m in mcPriceListItem)
            {
                var splitItem = Regex.Split(m.ToString(), "</td><td align=\"right\">");

                MostPriceList.Add(new itemPrice
                {
                    name = splitItem[0],
                    price1 = Convert.ToDecimal(ConvertToBGDecimal(splitItem[1])),
                    price2 = Convert.ToDecimal(ConvertToBGDecimal(splitItem[2])),
                    isNew = true
                });
            }
        }


        /// <summary>
        /// Метод обхождащ всички страници на ценовата листа и записващ ги в списък. Използва метода GetMostPagePriceList и свойството PagesCount
        /// </summary>
        public void GetMostFullPriceList()
        {
            string pageNameStr = "?pageNum=";

            for (int i = 1; i <= PagesCount; i++)
                GetMostPagePriceList(GetPriceLisetPageSourse(baseURL + pageNameStr + i.ToString()));
        }


        /// <summary>
        /// Свързва се с базата данни на unicoms_metronet и извлиза списък с всички продукти от сайта
        /// </summary>
        /// <param name="source">Име на хоста</param>
        /// <param name="username">Име на потребител на базата данни</param>
        /// <param name="password">Парола на потребител на базата данни</param>
        /// <param name="database">Име на базата данни</param>
        public void GetWebFullPriceList(string source, string username, string password, string database)
        {
            string conStr = @"Data Source=" + source + ";" + "Persist Security Info=yes;" + "UserId=" + username + "; PWD=" + password + ";database=" + database + "";
            MySqlConnection conDatabase = null;
            MySqlDataReader rdr = null;

            try
            {
                conDatabase = new MySqlConnection(conStr);
                conDatabase.Open();

                string stm = @"SELECT catalog_product_entity.entity_id AS entity_id,
                                      catalog_product_entity.sku AS sku,
                                      catalog_product_entity_text.value AS input_description,
                                      catalog_product_entity_int.value AS status,
                                      catalog_product_entity.updated_at AS last_update,
                                      catalog_product_entity_varchar.value AS supplyer_code, 
                                      catalog_product_entity_decimal.value AS price
        
                             FROM catalog_product_entity_decimal, catalog_product_entity, catalog_product_entity_varchar, catalog_product_entity_int, catalog_product_entity_text

                             WHERE catalog_product_entity.entity_id = catalog_product_entity_decimal.entity_id
                             AND catalog_product_entity.entity_id = catalog_product_entity_varchar.entity_id
                             AND catalog_product_entity.entity_id = catalog_product_entity_int.entity_id
                             AND catalog_product_entity.attribute_set_id =  '26'
                             AND catalog_product_entity_decimal.attribute_id =  '64'
                             AND catalog_product_entity_varchar.attribute_id =  '557'
                             AND catalog_product_entity_int.attribute_id =  '84'
                             AND catalog_product_entity_text.entity_id = catalog_product_entity.entity_id
                             AND catalog_product_entity_text.attribute_id = '556'
                             LIMIT 1600, 100";

                MySqlCommand cmd = new MySqlCommand(stm, conDatabase);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    WebList.Add(new WebStoreItem
                    {
                        entity_id = rdr.GetInt32(0),
                        sku = rdr.GetString(1),
                        input_description = rdr.GetString(2),
                        status = GetStatus(rdr.GetInt16(3)),
                        last_update = rdr.GetDateTime(4),
                        supplyer_code = rdr.GetString(5),
                        price = rdr.GetDecimal(6),
                        is_change = false
                    });
                }
            }
            catch (MySqlException ex)
            {
                Debug.Print(ex.ToString());
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }

                if (conDatabase != null)
                {
                    conDatabase.Close();
                }
            }
        }


        /// <summary>
        /// Контролен метод показващ в конзолата всички елемени на списъка от Мост
        /// </summary>
        public void ShowPriceList()
        {
            foreach (var listItem in MostPriceList)
            {
                Debug.WriteLine("{0,-40}{1,10}{2,10}", listItem.name, listItem.price1, listItem.price2);
            }
            Debug.WriteLine(MostPriceList.Count.ToString());
        }


        /// <summary>
        /// Контролен метод показващ в конзолата всички елемени на списъка от Юникомс
        /// </summary>
        public void ShowWebList()
        {
            Debug.WriteLine("{0,-40}{1,-30}{2,-10}{3,-25}{4,-20}{5,10}{6,10}", "sku   ", "input_description   ", "status   ", "last_update   ", "supplyer_code   ", "price", "is_change");
            foreach (var listItem in WebList)
            {
                Debug.WriteLine("{0, -10}{1,-40}{2,-30}{3,-10}{4,-25}{5,-20}{6,10}{7,10}", listItem.entity_id, listItem.sku, "input_description", listItem.status, listItem.last_update, listItem.supplyer_code, listItem.price, listItem.is_change);
            }
            Debug.WriteLine(WebList.Count.ToString());
        }


        /// <summary>
        /// Метод за проверка и корекция на десетична запетая/точка за правилно разпознаване на валутата
        /// </summary>
        /// <param name="input">сумата в начален формат - string</param>
        /// <returns>сумата в краен формат - string</returns>
        public string ConvertToBGDecimal(string input)
        {
            return Regex.Replace(input, "\\.", ",", RegexOptions.Singleline | RegexOptions.IgnoreCase);
        }


        /// <summary>
        /// Метод за закръгляне на едениците в цената
        /// </summary>
        /// <param name="value">Оригинална стойност</param>
        /// <returns>Закръглена стойност</returns>
        public decimal Round(decimal value)
        {
            decimal rounded = Math.Round(value, 0);
            decimal unitTruncated = Math.Truncate(Math.Truncate(value) / 10) * 10;
            int unit = (int)(rounded - unitTruncated);

            if (rounded > 30)
            {
                if (unit >= 0 && unit <= 3) return unitTruncated;
                else if (unit >= 4 && unit <= 6) return unitTruncated + 5;
                else if (unit >= 7 && unit <= 9) return unitTruncated + 9;
            }

            return rounded;
        }


        public int SetStatus(bool status)
        {
            if (status)
                return 1;
            else
                return 2;
        }
        public bool GetStatus(int status)
        {
            if (status == 1)
                return true;
            else
                return false;
        }


        public string RemovePrefix(string sku)
        {
            if (sku.StartsWith("IC-"))
                return sku.Substring(3);
            else if (sku.StartsWith("AC-"))
                return sku.Substring(3);
            else if (sku.StartsWith("AS-"))
                return sku.Substring(3);
            else if (sku.StartsWith("DELL-"))
                return sku.Substring(4);
            else if (sku.StartsWith("TH-"))
                return sku.Substring(3);
            else if (sku.StartsWith("HP-"))
                return sku.Substring(3);
            else if (sku.StartsWith("LNV-"))
                return sku.Substring(4);
            else if (sku.StartsWith("SONY-"))
                return sku.Substring(5);
            else if (sku.StartsWith("SAM-"))
                return sku.Substring(4);


            return sku;
        }


        /// <summary>
        /// Метод за сравняване на ценовата листа на Юникомс със аз на Мост
        /// </summary>
        public void UpdateMostPriceList()
        {


            foreach (var webItem in WebList)
            {
                bool samePrise = false;

                foreach (var mostItem in MostPriceList)
                {
                    if ((RemovePrefix(webItem.sku) == mostItem.name) && (webItem.supplyer_code == "Most"))
                    {
                        if ((Round(mostItem.price2 * usd * (decimal)1.2) == webItem.price) && (webItem.status == true))
                        {
                            mostItem.isNew = false;
                            samePrise = true;
                            break;
                        }
                        else
                        {
                            webItem.price = Round(mostItem.price2 * usd * (decimal)1.2);
                            webItem.last_update = DateTime.Now;
                            webItem.status = true;
                            webItem.is_change = true;
                            mostItem.isNew = false;
                            break;
                        }
                    }
                }

                //problem pri mnogokraten update bez nulirane na spisycite 
                //ako is_change e true ot predhodniq update dolnoto uslovie nqma da e korektno
                if ((webItem.status == true) && (webItem.is_change == false) && (webItem.supplyer_code == "Most") && (samePrise == false))
                {
                    webItem.status = false;
                    webItem.is_change = true;
                }
                samePrise = false;
            }

            Debug.WriteLine("Promeneni");
            Debug.WriteLine("{0,-40}{1,-30}{2,-10}{3,-25}{4,-20}{5,10}{6,10}", "sku   ", "input_description   ", "status   ", "last_update   ", "supplyer_code   ", "price", "is_change");
            foreach (var webitem in WebList)
            {
                if (webitem.is_change)
                    Debug.WriteLine("{0,-40}{1,-30}{2,-10}{3,-25}{4,-20}{5,10}{6,10}", webitem.sku, "input_description", webitem.status, webitem.last_update, webitem.supplyer_code, webitem.price, webitem.is_change);
            }

            Debug.WriteLine("\nNepromeneni\n\n\n");
           
            foreach (var webitem in WebList)
            {
                if (!(webitem.is_change))
                    Debug.WriteLine("{0,-40}{1,-30}{2,-10}{3,-25}{4,-20}{5,10}{6,10}", webitem.sku, "input_description", webitem.status, webitem.last_update, webitem.supplyer_code, webitem.price, webitem.is_change);
            }
        }



        /// <summary>
        /// Метод за експорт на списъка от Мост
        /// </summary>
        /// <param name="strFileName">Път и име на файла</param>
        /// <param name="strSheetName">Име на страницата</param>
        public void ExportToExcel(string strFileName, string strSheetName)
        {
            // Run the garbage collector
            GC.Collect();

            // Delete the file if it already exists
            if (System.IO.File.Exists(strFileName))
            {
                System.IO.File.SetAttributes(strFileName, FileAttributes.Normal);
                System.IO.File.Delete(strFileName);
            }

            // Open an instance of excel. Create a new workbook.
            // A workbook by default has three sheets, so if you just want a single one, delete sheet 2 and 3
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Excel._Workbook xlWB = (Excel._Workbook)xlApp.Workbooks.Add(Missing.Value);
            Excel._Worksheet xlSheet = (Excel._Worksheet)xlWB.Sheets[1];
            ((Excel._Worksheet)xlWB.Sheets[2]).Delete();
            ((Excel._Worksheet)xlWB.Sheets[2]).Delete();

            xlSheet.Name = strSheetName;
            // Write a value into A1
            xlSheet.Cells[1, 1] = "Name";
            xlSheet.Cells[1, 2] = "Price";
            xlSheet.Cells[1, 3] = "Is New";

            int i = 2;
            foreach (var mostItem in MostPriceList)
            {
                xlSheet.Cells[i, 1] = mostItem.name;
                xlSheet.Cells[i, 2] = mostItem.price2.ToString(); ;
                xlSheet.Cells[i, 3] = mostItem.isNew;
                i++;
            }

            // Tell Excel to save your spreadsheet
            xlWB.SaveAs(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            xlApp.Quit();

            // Release the COM object, set the Excel variables to Null, and tell the Garbage Collector to do its thing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWB);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

            xlSheet = null;
            xlWB = null;
            xlApp = null;

            GC.Collect();
        }

    }
}
